<?php

namespace Admin\Controller;

use User\Api\UserApi;
use Org\Util\Sync;
use Think\Model;
use Org\Util\Rest;
use Org\BMap; 
use Admin\Event;

/**
 * 报表控制器
 * @author James
 * 2015-07-23
 */
class ReportController extends AdminController {

    /**
     * 线上水果销售报表
     * @author James
     * 2015-07-23
     */
    public function sales($pname="") {
        $Model = new Model();
        $report_day1 = strtotime(I('report_day1'));
        $report_day2 = strtotime(I('report_day2'));
        if (!$report_day1) {
            $report_day1 = strtotime(date("Y-m-d 12:00:00",time()-86400));
        }
        if (!$report_day2) {
            $report_day2 = time();
        }
        $this->assign('report_day1', $report_day1);
        $this->assign('report_day2', $report_day2);
        $sql = "select a.pname name,a.quantity,count(DISTINCT a.orderid) o_cnt,sum(a.cartnum) scnt,ROUND(sum(a.totalprice)) total,count(DISTINCT a.uid) u_cnt
            from gr_vorder_goods a,gr_order b
            where a.orderid = b.orderid
            and b.status >= 2000 and b.status <4000
            and b.createDate >= '".$report_day1."' and b.createDate <= '".$report_day2."'";

        if($pname!=""){
            $sql.=" and a.pname like '%".$pname."%'";
        }
        $sql.= " group by a.pname,a.quantity order by sum(a.cartnum) desc";
        $list = $Model->query($sql);
        $this->pname=$pname;
        $this->assign('_list', $list);
        $this->display();
    }
    /**
     * 线上水果销售报表 导出
     * @author James
     * 2015-07-23
     */
    public function onlineSalesExport($pname="") {
        
        $Model = new Model();
        $report_day1 = strtotime(I('report_day1'));
        $report_day2 = strtotime(I('report_day2'));
        if (!$report_day1) {
            $report_day1 = strtotime(date("Y-m-d",time()));
        }
        if (!$report_day2) {
            $report_day2 = strtotime(date("Y-m-d",time()))+3600*24-1;
        }

        $sql = "select Concat(a.pname, '(', a.quantity, ')' ) name,count(DISTINCT a.orderid) o_cnt,sum(a.cartnum) scnt,ROUND(sum(a.totalprice)) total,count(DISTINCT a.uid) u_cnt
            from gr_vorder_goods a,gr_order b
            where a.orderid = b.orderid
            and b.status >= 2000 and b.status <4000
            and b.createDate >= '".$report_day1."' and b.createDate <= '".$report_day2."'";

        if($pname!=""){
            $sql.=" and a.pname like '%".$pname."%'";
        }
        $sql.=" group by a.pname,a.quantity
            order by sum(a.cartnum) desc";
        $data = $Model->query($sql);
        
        if ($data) {
            //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
            import("Org.Util.PHPExcel");
            import("Org.Util.PHPExcel.Writer.Excel5");
            import("Org.Util.PHPExcel.IOFactory.php");

            $filename = "output_order_excel";
 
            $headArr = array("产品名称", "订单数","商品数量", "金额","订单用户数");
            
            $title = "线上水果销售报表(配货人:_________联合配货人:_________________收货人:________)";
            $this->getExcel($filename, $headArr, $data, $title);
        }else{
            $this->error("没有可导出的数据！");
        }
    }
    /**
     * 日报用户数据
     * @author James
     * 2015-07-29
     */
    public function daily() {

        $Model = new Model();
        
        $report_day1 = I('report_day1');
        $report_day2 = I('report_day2');
        
        if (!$report_day1) {
            $report_day1 = substr(Now(),0,8)."01";
        }
        
        if (!$report_day2) {
            $report_day2 = substr(Now(),0,10);
        }
        
        $this->assign('report_day1', $report_day1);
        $this->assign('report_day2', $report_day2);
        $sql = "select a.report_day,
            count(distinct a.uid) ucnt,
            (select count(b.tdorderid) from gr_vreject_out  b where a.report_day = b.report_day )totalrejectcnt,
            (select sum(ABS(b.tdtotalprice)) from gr_vreject_out b where a.report_day = b.report_day )totalrejectprice,
            count(1) ocnt,
            sum(useamount) useamount,
            ROUND(sum(expectamount)) amount
            from gr_vorder a where a.report_day >= '".$report_day1."' and a.report_day <= '".$report_day2."'
            and a.status >= 2000
            and a.status <> 4000
            group by a.report_day
            order by a.report_day desc";
        $list = $Model->query($sql);
        $report_day="";
        $finalAmountArray="";
        $totalrejectprice="";
        for ($i = count($list)-1; $i >-1 ; $i--) {
            $strdate = $list[$i]['report_day'];
            $strdate = str_replace('-', '', $strdate);
            $strdate = substr($strdate, 6);
            $report_day = $report_day . $strdate . ',';
            $finalAmountArray .= $list[$i]['amount'] . ',';
            if ($list[$i]['totalrejectprice'] == null) {
                $list[$i]['totalrejectprice'] = 0;
            }
            $totalrejectprice .= $list[$i]['totalrejectprice'] . ',';
        }

        $finalAmountArray= substr($finalAmountArray,0,strlen($finalAmountArray)-1);
        $totalrejectprice= substr($totalrejectprice,0,strlen($totalrejectprice)-1);
        $report_day= substr($report_day,0,strlen($report_day)-1);
        
        $this->assign('report_day', $report_day);
        $this->assign('finalAmountArray', $finalAmountArray);
        $this->assign('totalrejectprice', $totalrejectprice);
        $this->assign('_list', $list);
        $this->display();
    }
    /**
     * 线上水果销售报表（日报）导出
     * @author James
     * 2015-07-23
     */
    public function dailyExport() {
        
        $Model = new Model();
        
        $report_day1 = I('report_day1');
        $report_day2 = I('report_day2');
        
//        $sql = "select report_day,
//            count(distinct a.uid) ucnt,
//            count(1) ocnt,
//            sum(amount) amount,
//            FORMAT(sum(amount)/count(1),2) price
//            from gr_vorder a where a.report_day >= '".$report_day1."' and a.report_day <= '".$report_day2."'
//            and a.status >= 2000
//            and a.status <> 4000
//            group by a.report_day
//            order by a.report_day desc";
        $sql = "select a.report_day,
            count(distinct a.uid) ucnt,
            count(1) ocnt,
            ROUND(sum(expectamount)) amount,
            sum(useamount) useamount,
            FORMAT(sum(amount)/count(1),2) price,
             (select count(b.tdorderid) from gr_vreject_out  b where a.report_day = b.report_day )totalrejectcnt ,
            (select sum(ABS(b.tdtotalprice)) from gr_vreject_out b where a.report_day = b.report_day )totalrejectprice
            from gr_vorder a where a.report_day >= '".$report_day1."' and a.report_day <= '".$report_day2."'
            and a.status >= 2000
            and a.status <> 4000
            group by a.report_day
            order by a.report_day desc";
        $data = $Model->query($sql);

        if ($data) {
            //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
            import("Org.Util.PHPExcel");
            import("Org.Util.PHPExcel.Writer.Excel5");
            import("Org.Util.PHPExcel.IOFactory.php");

            $filename = "output_order_excel";
 
            $headArr = array("日期","用户数", "总订单数", "交易额(含余额消费)","余额消费","客单价","退单数","退单金额");
            
            $title = "日报用户数据";
            $this->getExcel($filename, $headArr, $data, $title);
        }else{
            $this->error("没有可导出的数据！");
        }
    }
    /**
     * 线上水果销售报表（日报）导出
     * @author James
     * 2015-07-23
     */
    public function dailyDetailExport() {
        
        $Model = new Model();
        
        $report_day = I('report_day');
     
        $sql = "select a.report_day,concat(b.pname,b. ) pname,
            count(distinct a.uid) ucnt,
            count(distinct b.orderid) ocnt,
            sum(b.totalprice) amount,
            FORMAT(sum(b.totalprice)/count(distinct b.orderid),2) price
            from gr_vorder a,gr_vorder_goods b 
            where a.orderid = b.orderid
            and a.report_day = '".$report_day."'
            and a.status >= 2000
            and a.type in (0,2,3)
            group by a.report_day,b.pname,b.specname
            order by b.pname,b.specname ";
        $data = $Model->query($sql);
        
        if ($data) {
            //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
            import("Org.Util.PHPExcel");
            import("Org.Util.PHPExcel.Writer.Excel5");
            import("Org.Util.PHPExcel.IOFactory.php");

            $filename = "output_order_excel";
 
            $headArr = array("日期","产品名称","用户数", "总订单数", "总收入","平均客单价");
            
            $title = "日报用户数据";
            $this->getExcel($filename, $headArr, $data, $title);
        }else{
            $this->error("没有可导出的数据！");
        }
    }
    /**
     * 地区销售数据
     */
    public function areasale($start_date="",$end_date=""){
        if(!$end_date){
            $end_date=date('Y-m-d',time());
        }else{
            $this->end_date=$end_date;
        }
        if(!$start_date){
            $start_date=$end_date;
        }else{
            $this->start_date=$start_date;
        }

        $model=new Model();
        $sql="select sum(a.amount) totalAmount,(select sum(ABS(b.tdtotalprice))
            from gr_vreject_out b,gr_order c where c.toCity=a.toCity and b.oldorderid=c.orderid
            and  b.report_day<='".$end_date."' and b.report_day>='".$start_date."' )totalreject,a.toCity from gr_vorder a
            where a.report_day>='".$start_date."' and a.report_day<='".$end_date."'
            group by a.toCity order by sum(a.amount) desc";
        $res=$model->query($sql);
        $this->assign('_list',$res);
        $this->assign('list',json_encode($res));
        $this->title="地区销售数据";
        $this->display();
    }
    public function areaExport($start_date="",$end_date=""){
        if(!$end_date){
            $end_date=date('Y-m-d',time());
        }else{
            $this->end_date=$end_date;
        }
        if(!$start_date){
            $start_date=$end_date;
        }else{
            $this->start_date=$start_date;
        }

        $model=new Model();
        $sql="select a.toCity,sum(a.amount) totalAmount,(select sum(ABS(b.tdtotalprice))
            from gr_vreject_out b,gr_order c where c.toCity=a.toCity and b.oldorderid=c.orderid
            and  b.report_day<='".$end_date."' and b.report_day>='".$start_date."' )totalreject from gr_vorder a
            where a.report_day>='".$start_date."' and a.report_day<='".$end_date."'
            group by a.toCity order by sum(a.amount) desc";
        $data=$model->query($sql);
        if ($data) {
            //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
            import("Org.Util.PHPExcel");
            import("Org.Util.PHPExcel.Writer.Excel5");
            import("Org.Util.PHPExcel.IOFactory.php");

            $filename = "output_order_excel";

            $headArr = array("地区","成交额", "退货金额");

            $title = $start_date."--".$end_date."地区销售数据";
            $this->getExcel($filename, $headArr, $data, $title);
        }else{
            $this->error("没有可导出的数据！");
        }
    }
    /**
     * 退货商品统计
     */
    public function reject($start_time="",$end_time=""){
        $Model = new Model();
        $sql="SELECT SUM(a.tdtotalprice) totalprice,b.tdpname,COUNT(a.tdorderid) ocnt,COUNT(b.tdpname) pcnt FROM gr_reject a,gr_reject_goods b WHERE a.tdorderid=b.tdorderid";
        if($start_time!=""){
            $start_time=strtotime($start_time);
        }else{
            $start_time=strtotime(date('Y-m-d 12:00:00',time()-(24*3600)));
        }
        if($end_time!=""){
            $end_time=strtotime($end_time);
        }else{
            $end_time=time();
        }
        $sql.=" AND a.createtime>".$start_time." AND a.createtime<".$end_time." GROUP by b.tdpname";
        $res= $Model->query($sql);
        $this->end_time=$end_time;
        $this->start_time=$start_time;
        $this->assign("_list",$res);
        $this->display();
    }
    public function rejectExport($start_time="",$end_time="") {

        $Model = new Model();

        $start_time = strtotime($start_time);
        $end_time = strtotime($end_time);
        $sql="SELECT b.tdpname,SUM(a.tdtotalprice) totalprice,COUNT(b.tdpname) pcnt,COUNT(a.tdorderid) ocnt FROM gr_reject a,gr_reject_goods b WHERE a.tdorderid=b.tdorderid";
        $sql.=" AND a.createtime>".$start_time." AND a.createtime<".$end_time." GROUP by b.tdpname";
        $data = $Model->query($sql);

        if ($data) {
            //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
            import("Org.Util.PHPExcel");
            import("Org.Util.PHPExcel.Writer.Excel5");
            import("Org.Util.PHPExcel.IOFactory.php");

            $filename = "output_order_excel";

            $headArr = array("商品","总价", "退货数", "退货订单");

            $title = "退货用户数据";
            $this->getExcel($filename, $headArr, $data, $title);
        }else{
            $this->error("没有可导出的数据！");
        }
    }
    public function rejectdetail($pname="",$start_time="",$end_time=""){
        $model = new Model();
        $sql="SELECT a.tdpname,b.tdmark,b.toName,b.toPhone,b.tdtotalprice,b.createtime FROM gr_reject_goods a,gr_reject b WHERE a.tdorderid=b.tdorderid";
        $sql.=" and a.tdpname='".$pname."' and b.createtime>".$start_time.' and b.createtime<'.$end_time;
        $res = $model->query($sql);
        $this->pname=$pname;
        $this->start_time=$start_time;
        $this->end_time=$end_time;
        $this->assign("_list",$res);
        $this->display();
    }
    public function rejectDetailExport($pname="",$start_time="",$end_time="") {
        $Model = new Model();
        $sql="SELECT a.tdpname,b.toName,b.toPhone,b.tdtotalprice,b.tdmark,date_format(b.createtime,'%Y-%m-%d %H:%i:%s') FROM gr_reject_goods a,gr_reject b WHERE a.tdorderid=b.tdorderid";
        $sql.=" and a.tdpname='".$pname."' and b.createtime>".$start_time.' and b.createtime<'.$end_time;
        $data = $Model->query($sql);
        if ($data) {
            //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
            import("Org.Util.PHPExcel");
            import("Org.Util.PHPExcel.Writer.Excel5");
            import("Org.Util.PHPExcel.IOFactory.php");

            $filename = "output_order_excel";

            $headArr = array("商品","退货人","联系方式","总价", "原因", "退货时间");

            $title = "退货用户数据";
            $this->getExcel($filename, $headArr, $data, $title);
        }else{
            $this->error("没有可导出的数据！");
        }
    }
    public function amount(){
        $Model = new Model();
        $sql = "select sum(amount) amount from gr_buyers a where a.status = 1 ";
        $useramount = $Model->query($sql);
        $this->assign('useramount', $useramount[0]);
        $this->display();
    }
    public function week() {

        $Model = new Model();
        
        $report_day1 = I('report_day1');
        $report_day2 = I('report_day2');
        
        if (!$report_day1) {
            $report_day1 = substr(Now(),0,10);
        }
        
        if (!$report_day2) {
            $report_day2 = substr(Now(),0,10);
        }
        
//        $sql = "select 
//            a.report_day,case dayofweek(str_to_date(report_day,'%Y-%m-%d'))  
//            when 1 then '星期日' 
//            when 2 then '星期一' 
//            when 3 then '星期二' 
//            when 4 then '星期三' 
//            when 5 then '星期四' 
//            when 6 then '星期五' 
//            when 7 then '星期六' end week,
//            count(1) cnt,
//            (select count(1) from gr_vorder b where b.report_day = date_format(str_to_date(a.report_day,'%Y-%m-%d')-7,'%Y-%m-%d')) cnt_last,
//            count(distinct case when a.nextflag <= 3 then a.uid end) xyhs,
//            (select count(distinct case when b.nextflag <= 3 then b.uid end) from gr_vorder b where b.report_day = date_format(str_to_date(a.report_day,'%Y-%m-%d')-7,'%Y-%m-%d')) xyhs_last,
//            count(distinct case when a.nextflag > 3 then a.uid end) lyhs,
//            (select count(distinct case when b.nextflag > 3 then b.uid end) from gr_vorder b where b.report_day = date_format(str_to_date(a.report_day,'%Y-%m-%d')-7,'%Y-%m-%d')) lyhs_last,
//            sum(amount) amount,
//            ifnull((select sum(amount) from gr_vorder b where b.report_day = date_format(str_to_date(a.report_day,'%Y-%m-%d')-7,'%Y-%m-%d')),0) amount_last
//            from gr_vorder a 
//            where a.report_day >= '".$report_day1."'
//            and a.report_day <= '".$report_day2."'
//            and a.type in (0,2,3)
//            group by a.report_day
//            order by a.report_day";
        
        $sql = "select 
            a.report_day,case dayofweek(str_to_date(report_day,'%Y-%m-%d'))  
            when 1 then '星期日' 
            when 2 then '星期一' 
            when 3 then '星期二' 
            when 4 then '星期三' 
            when 5 then '星期四' 
            when 6 then '星期五' 
            when 7 then '星期六' end week,
            count(1) cnt,
            count(distinct case when a.nextflag <= 3 then a.uid end) xyhs,
            count(distinct case when a.nextflag > 3 then a.uid end) lyhs,
            sum(amount) amount,
            FORMAT(sum(finalAmount),2) finalAmount
            from gr_vorder a 
            where a.report_day >= '".$report_day1."'
            and a.report_day <= '".$report_day2."'
            and a.type in (0,2,3)
            and a.status >= 2000
            group by a.report_day
            order by a.report_day";
        $list = $Model->query($sql);
        
        $this->assign('_list', $list);
        $this->assign('report_day1', $report_day1);
        $this->assign('report_day2', $report_day2);
        $this->display();
    }
    private function getExcel($fileName, $headArr, $data, $title) {
        //对数据进行检验
        if (empty($data) || !is_array($data)) {
            //die("data must be a array");
        }
        //检查文件名
        if (empty($fileName)) {
            exit;
        }
        // H:i:s
        $date = date("Y_m_d_H_i_s", time());
        //$date = date("Y_m_d",time());
        $fileName .= "_{$date}.xls";

        //创建PHPExcel对象，注意，不能少了\
        $objPHPExcel = new \PHPExcel();
        $objProps = $objPHPExcel->getProperties();

        //第一列设置报表时间范围
        $key = ord("A");
        $colum = chr($key);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $title);
        //第二列设置报表列头
        $key = ord("A");
        foreach ($headArr as $v) {
            $colum = chr($key);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '2', $v);
            $objPHPExcel->getActiveSheet()->getStyle($colum . '2')->getFont()->setBold(true);
            $key += 1;
        }
        $column = 3;
        $objActSheet = $objPHPExcel->getActiveSheet();
        foreach ($data as $key => $rows) { //行写入
            $span = ord("A");
            foreach ($rows as $keyName => $value) {// 列写入
                $j = chr($span);
                $objActSheet->setCellValue($j . $column, $value);
                $span++;
            }
            $column++;
        }
        $objActSheet->getColumnDimension('A')->setWidth(18);
        $objActSheet->getColumnDimension('B')->setWidth(16);
        $objActSheet->getColumnDimension('C')->setWidth(10);
        $objActSheet->getColumnDimension('D')->setWidth(25);
        $objActSheet->getColumnDimension('E')->setWidth(15);
        $objActSheet->getColumnDimension('F')->setWidth(20);
        $objActSheet->getColumnDimension('G')->setWidth(20);
        $objActSheet->getColumnDimension('H')->setWidth(15);
        $objActSheet->getColumnDimension('I')->setWidth(80);
        $fileName = iconv("utf-8", "gb2312", $fileName);
        //重命名表
        // $objPHPExcel->getActiveSheet()->setTitle('test');
        //设置活动单指数到第一个表,所以Excel打开这是第一个表
        $objPHPExcel->setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=\"$fileName\"");
        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output'); //文件通过浏览器下载
        exit;
    }

    function sellsreportdaliy($beginTime = null, $endTime = null){
        if ($beginTime == null || $endTime == null) {

        } else {
            $beginTimeOutPut = $beginTime;
            $endTimeOutPut = $endTime;

            $dtres=$this->getsellreportdaliy($beginTime,$endTime);

            $this->assign("_list",$dtres);
            //$this->assign('dateFlag', $dateFlag);
            $this->assign('beginTime', $beginTimeOutPut);
            $this->assign('endTime', $endTimeOutPut);
        }
        $this->display();
    }

    function  getsellreportdaliy($beginTime = null, $endTime = null){
        $beginTimeStr=$beginTime;
        $beginTime = strtotime($beginTime);
        $endTime = strtotime($endTime);

        $dateFlag="";
        $dateFlagBegin = date('y-m-d', strtotime($beginTimeStr.'+1 day'));
        $dateFlagEnd = date('y-m-d', $endTime);
        if($dateFlagBegin==$dateFlagEnd){
            $dateFlag=$dateFlagEnd;
        }else{
            $dateFlag= date('y-m', $endTime);
        }

//        $beginTime = strtotime($dateFlagBegin.'-1 day'.' 12:00');
//        $endTime = strtotime($dateFlagEnd.' 12:00');

        $Model = new Model();
        $sql="select '".$dateFlag."' as dateFlag,t7.sname,t7.totalamount,t7.totalcount,ifnull(t7.tdtotalprice,0) as tdtotalprice,concat(ifnull(t7.tdtotalpricerate,0),'%') as tdtotalpricerate,FORMAT(t7.totalamount/t7.totalcount,2) as personalamount,ifnull(-t8.no_goods_total,0) as no_goods_total,concat(ifnull(FORMAT((-t8.no_goods_total/t7.totalamount)*100,2),0),'%') as no_goods_total_rate,ifnull(-t8.low_quantity_total,0) as low_quantity_total,concat(ifnull(FORMAT((-t8.low_quantity_total/t7.totalamount)*100,2),0),'%') as low_quantity_total_rate ,ifnull(-t8.no_reason_total,0) as no_reason_total,concat(ifnull(FORMAT((-t8.no_reason_total/t7.totalamount)*100,2),0),'%') as no_reason_total_rate,t10.activeusercount from (select t2.*,t3.tdtotalprice,FORMAT((t3.tdtotalprice/t2.totalamount)*100,2) as tdtotalpricerate from (SELECT r.sid,r.sname, sum(o.amount) as totalamount,count(o.orderid)as totalcount FROM `gr_order` o inner join `gr_relationship` r on o.uid=r.uid where o.createDate>$beginTime and o.createDate<$endTime and (o.status=3000 or o.status=2000) group by r.sid) t2 left join ( select -sum(t.tdtotalprice) as tdtotalprice,r.sname,r.sid from (SELECT * FROM `gr_reject` where oldorderid in (select orderid FROM `gr_order` where createDate>$beginTime and createDate<$endTime and (status=3000 or status=2000))) t inner join `gr_order` o on t.oldorderid=o.orderid inner join `gr_relationship` r on o.uid=r.uid group by r.sid) t3 on t2.sid=t3.sid) t7 left join (select t6.sid,t6.sname,sum(t6.no_goods_total) as no_goods_total,sum(t6.low_quantity_total) as low_quantity_total,sum(t6.no_reason_total)as no_reason_total from(select t5.sid,t5.sname,case t5.td_type when 'no_goods' then t5.subtdtotalprice ELSE 0 END as no_goods_total,case t5.td_type when 'low_quantity' then t5.subtdtotalprice ELSE 0 END as low_quantity_total,case t5.td_type when 'no_reason' then t5.subtdtotalprice ELSE 0 END as no_reason_total from ( select r.sid,r.sname,rg.td_type,sum(tdtotalprice) as subtdtotalprice from (SELECT tdorderid,oldorderid FROM `gr_reject` where oldorderid in (select orderid FROM `gr_order` where createDate>$beginTime and createDate<$endTime and (status=3000 or status=2000))) t4 inner join `gr_reject_goods` rg on t4.tdorderid=rg.tdorderid inner join `gr_order` o on t4.oldorderid=o.orderid inner join `gr_relationship` r on o.uid=r.uid group by r.sid,rg.td_type) t5 group by t5.sid,t5.td_type) t6 group by t6.sid  ) t8 on t7.sid=t8.sid left join (select t9.sid,count(t9.uid)as activeusercount from (select r.sid,o.uid FROM `gr_order`o inner join `gr_relationship` r on o.uid=r.uid where createDate>$beginTime and createDate<$endTime and (status=3000 or status=2000) group by r.sid,o.uid) t9 group by t9.sid) t10 on t7.sid=t10.sid";
        $dtres=$Model->query($sql);
        return $dtres;
    }

    public function exportsellsreportdaliy($beginTime = null, $endTime = null)
    {
        if (!IS_POST) {
            $this->redirect("Report/sellsreportdaliy");
        } else {
            if ($beginTime == null || $endTime == null) {
                $this->error("参数有误！");
            } else {
//                $dateFlagBegin = strtotime($beginTime.'+1 day');
//                $dateFlagEnd = strtotime($endTime);
//                $dateFlagBegin = date('y-m-d', $dateFlagBegin);
//                $dateFlagEnd = date('y-m-d', $dateFlagEnd);
                $dataTable = $this->getsellreportdaliy($beginTime,$endTime);
                if ($dataTable) {

                    //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
                    import("Org.Util.PHPExcel");
                    import("Org.Util.PHPExcel.Writer.Excel5");
                    import("Org.Util.PHPExcel.IOFactory.php");

                    $filename = "$beginTime 至 $endTime 销售报表（业务员）";

                    $headArr = array("日期", "业务员", "成交金额", "总单数", "退款金额", "退款率", "平均客单价", "无货退款", "退款率", "质量退款", "退款率", "非质量退款", "退款率", "活跃客户数");

                    $title = "销售报表（业务员）";
                    $this->getExcel_report($filename, $headArr, $dataTable, $title);
                } else {
                    $this->error("没有可导出的数据！");
                }
            }
        }
    }
    /**
     * 导出共用方法
     */
    public function getExcel_report($fileName, $headArr, $data, $title)
    {
        //对数据进行检验
        if (empty($data) || !is_array($data)) {
            //die("data must be a array");
        }
        //检查文件名
        if (empty($fileName)) {
            exit;
        }
        // H:i:s
        $date = date("Y_m_d_H_i_s", time());
        //$date = date("Y_m_d",time());
        $fileName .= "_{$date}.xls";

        //创建PHPExcel对象，注意，不能少了\
        $objPHPExcel = new \PHPExcel();
        $objProps = $objPHPExcel->getProperties();

        //第一列设置报表时间范围
        $key = ord("A");
        $colum = chr($key);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $title);

        //第二列设置报表列头
        $key = ord("A");
        foreach ($headArr as $v) {
            $colum = chr($key);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '2', $v);
            $key += 1;
        }

        $column = 3;
        $objActSheet = $objPHPExcel->getActiveSheet();
        foreach ($data as $key => $rows) { //行写入
            $span = ord("A");
            foreach ($rows as $keyName => $value) {// 列写入
                $j = chr($span);
                $objActSheet->setCellValue($j . $column, $value);
                $span++;
            }
            $column++;
        }

        $fileName = iconv("utf-8", "gb2312", $fileName);
        //重命名表
        // $objPHPExcel->getActiveSheet()->setTitle('test');
        //设置活动单指数到第一个表,所以Excel打开这是第一个表
        $objPHPExcel->setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=\"$fileName\"");
        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output'); //文件通过浏览器下载
        exit;
    }

    function sellsreportshops($beginTime = null, $endTime = null){
        if ($beginTime == null || $endTime == null) {

        } else {
            $beginTimeOutPut = $beginTime;
            $endTimeOutPut = $endTime;

            $dtres=$this->getsellreportshops($beginTime,$endTime);

            $this->assign("_list",$dtres);
            //$this->assign('dateFlag', $dateFlag);
            $this->assign('beginTime', $beginTimeOutPut);
            $this->assign('endTime', $endTimeOutPut);
        }
        $this->display();
    }

    function  getsellreportshops($beginTime = null, $endTime = null){
        $beginTimeStr=$beginTime;
        $beginTime = strtotime($beginTime);
        $endTime = strtotime($endTime);

        $dateFlag="";
        $dateFlagBegin = date('y-m-d', strtotime($beginTimeStr.'+1 day'));
        $dateFlagEnd = date('y-m-d', $endTime);
        if($dateFlagBegin==$dateFlagEnd){
            $dateFlag=$dateFlagEnd;
        }else{
            $dateFlag= date('y-m', $endTime);
        }

//        $beginTime = strtotime($dateFlagBegin.'-1 day'.' 12:00');
//        $endTime = strtotime($dateFlagEnd.' 12:00');

        $Model = new Model();
        $sql="select '".$dateFlag."' as dateFlag,t7.sname,t7.shopName,t7.realName,t7.phone,t7.street,t7.totalamount,t7.totalcount,ifnull(t7.tdtotalprice,0)as tdtotalprice,concat(ifnull(t7.tdtotalprice_rate,0),'%') as tdtotalprice_rate,format(t7.totalamount/t7.totalcount,2) as personalamount,ifnull(-t8.no_goods_total,0) as no_goods_total,concat(ifnull(format(-t8.no_goods_total/t7.totalamount*100,2),0),'%') as no_goods_total_rate,ifnull(-t8.low_quantity_total,0) as low_quantity_total,concat(ifnull(format(-t8.low_quantity_total/t7.totalamount*100,2),0),'%') as low_quantity_total_rate,ifnull(-t8.no_reason_total,0) as no_reason_total,concat(ifnull(format(-t8.no_reason_total/t7.totalamount*100,2),0),'%') as no_reason_total_rate from (select t2.*,-t3.tdtotalprice as tdtotalprice,FORMAT(-t3.tdtotalprice/t2.totalamount*100,2) as tdtotalprice_rate from (select r.sname,t.* from (select buyer.id,buyer.shopName,buyer.realName,buyer.phone,buyer.street,sum(o.amount) as totalamount,count(o.id) as totalcount FROM `gr_order` o inner join `gr_buyers` buyer on o.uid=buyer.id where o.createDate>$beginTime and o.createDate<$endTime and (o.status=3000 or o.status=2000) group by buyer.id) t inner join `gr_relationship` r on t.id=r.uid order by r.sid) t2 left join (select buyer.id,buyer.shopName,sum(r.tdtotalprice) as tdtotalprice from (select * from `gr_reject` where oldorderid in(select orderid FROM `gr_order`  where createDate>$beginTime and createDate<$endTime and (status=3000 or status=2000)))r inner join `gr_order` o  on r.oldorderid=o.orderid inner join `gr_buyers` buyer on o.uid=buyer.id group by buyer.id)t3 on t2.id=t3.id)t7 left join (select t6.uid,sum(t6.no_goods_total) as no_goods_total,sum(t6.low_quantity_total) as low_quantity_total,sum(t6.no_reason_total) as no_reason_total from (select t5.uid,case t5.td_type when 'no_goods' then subtdtotalprice else 0 end as no_goods_total,case t5.td_type when 'low_quantity' then subtdtotalprice else 0 end as low_quantity_total,case t5.td_type when 'no_reason' then subtdtotalprice else 0 end as no_reason_total from (select o.uid,rg.td_type,sum(tdtotalprice) as subtdtotalprice from (SELECT tdorderid,oldorderid FROM `gr_reject` where oldorderid in (select orderid FROM `gr_order` where createDate>$beginTime and createDate<$endTime and (status=3000 or status=2000))) t4 inner join `gr_reject_goods` rg on t4.tdorderid=rg.tdorderid inner join `gr_order` o on t4.oldorderid=o.orderid group by o.uid,rg.td_type) t5) t6 group by t6.uid )t8 on t7.id=t8.uid";
        $dtres=$Model->query($sql);
        return $dtres;
    }

    public function exportsellsreportshops($beginTime = null, $endTime = null)
    {
        if (!IS_POST) {
            $this->redirect("Report/sellsreportshops");
        } else {
            if ($beginTime == null || $endTime == null) {
                $this->error("参数有误！");
            } else {
//                $dateFlagBegin = strtotime($beginTime);
//                $dateFlagEnd = strtotime($endTime);
//                $dateFlagBegin = date('y-m-d', $dateFlagBegin);
//                $dateFlagEnd = date('y-m-d', $dateFlagEnd);
                $dataTable = $this->getsellreportshops($beginTime,$endTime);
                if ($dataTable) {

                    //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
                    import("Org.Util.PHPExcel");
                    import("Org.Util.PHPExcel.Writer.Excel5");
                    import("Org.Util.PHPExcel.IOFactory.php");

                    $filename = "$beginTime 至 $endTime 销售报表（商家）";

                    $headArr = array("日期", "业务员", "店名", "老板", "电话", "地址", "成交金额", "总单数", "退款金额", "退款率", "平均客单价", "无货退款", "退款率", "质量退款", "退款率", "非质量退款", "退款率");

                    $title = "销售报表（商家）";
                    $this->getExcel_report($filename, $headArr, $dataTable, $title);
                } else {
                    $this->error("没有可导出的数据！");
                }
            }
        }
    }
}
